package com.wanfajie.smsdistribute.db.repository.impl

import com.j256.ormlite.dao.Dao
import com.j256.ormlite.stmt.ColumnArg
import com.j256.ormlite.stmt.QueryBuilder
import com.j256.ormlite.stmt.Where
import com.wanfajie.smsdistribute.db.bean.phone.PhoneFieldValue
import com.wanfajie.smsdistribute.db.bean.phone.PhoneGroup
import com.wanfajie.smsdistribute.db.bean.phone.PhoneKeyField
import com.wanfajie.smsdistribute.db.bean.phone.PhoneNumber
import com.wanfajie.smsdistribute.db.helper.PhoneDatabaseHelper
import com.wanfajie.smsdistribute.db.repository.PlaceholderRepository

object PlaceholderRepositoryImpl: PlaceholderRepository {

    private val dao: Dao<PhoneKeyField, Int> = PhoneDatabaseHelper.getDao(PhoneKeyField::class.java)
    private val valueDao: Dao<PhoneFieldValue, Int> = PhoneDatabaseHelper.getDao(PhoneFieldValue::class.java)
    private val groupDao: Dao<PhoneGroup, Int> = PhoneDatabaseHelper.getDao(PhoneGroup::class.java)
    private val numberDao: Dao<PhoneNumber, Int> = PhoneDatabaseHelper.getDao(PhoneNumber::class.java)

    override fun queryAll(): List<PhoneKeyField> {
        return dao.queryForAll()
    }

    private fun filterEmpty(
            where: Where<PhoneKeyField, Int>,
            valueBuilder: QueryBuilder<PhoneFieldValue, Int>? = null
    ): Where<PhoneKeyField, Int> {

        val valBuilder = valueBuilder ?: valueDao.queryBuilder()
        valBuilder.selectColumns(PhoneFieldValue.FIELD_ID)
                .where()
                .eq(PhoneFieldValue.FIELD_ID,
                        ColumnArg(PhoneKeyField.TABLE_NAME, PhoneKeyField.ID))
                .and()
                .ne(PhoneFieldValue.VALUE_FIELD, "")
        valBuilder.limit(1)

        return where.exists(valBuilder)
    }

    override fun queryAll(notEmpty: Boolean): List<PhoneKeyField> {
        if (!notEmpty) return queryAll()

        val builder = dao.queryBuilder()
        filterEmpty(builder.where())

        return builder.query()
    }

    private fun buildGroupQuery(group: PhoneGroup): QueryBuilder<PhoneKeyField, Int> {
        val builder = dao.queryBuilder()
        val valBuilder = valueDao.queryBuilder()
        val groupBuilder = groupDao.queryBuilder()
        val numberBuilder = numberDao.queryBuilder()

        groupBuilder.where()
                .eq(PhoneGroup.ID, group.id)

        numberBuilder.leftJoin(groupBuilder)
        valBuilder.leftJoin(numberBuilder)

        filterEmpty(builder.where(), valBuilder)

        return builder
    }

    override fun queryByGroup(group: PhoneGroup): List<PhoneKeyField> {
        return buildGroupQuery(group).query()
    }

    override fun queryAllNames(): List<String> {
        val builder = dao.queryBuilder()
        builder.selectColumns(PhoneKeyField.NAME)
        filterEmpty(builder.where())
        val raw = builder.queryRaw()
        val result = raw.results.map {
            it[0]
        }
        raw.close()

        return result
    }

    override fun queryNameByGroup(group: PhoneGroup): List<String> {
        val raw = buildGroupQuery(group)
                .selectColumns(PhoneKeyField.NAME)
                .queryRaw()

        val result = raw.results.map {
            it[0]
        }

        raw.close()
        return result
    }
}